-- @owner: ningyali
-- @date: 2024-08-12
-- @testpoint: 表约束-unique

--step1:创建测试表; expect:执行成功
drop table if exists t_ustore_count_optim_0012;
create table t_ustore_count_optim_0012(
	c_id int  primary key, c_int  integer, c_bool bool,
	c_real real,
	c_char char(300),c_varchar2 varchar2(4000) default lpad('ee2t2xnbzzkih',80,'etskbfw@p%my8tesjbemus6gnv3'),
	c_timestamp timestamp not null
	) with (storage_type=ustore);

--step2:创建序列; expect: 执行成功
drop sequence if exists seq_ustore_count_optim_0012;
create sequence seq_ustore_count_optim_0012 increment by 1 start with 100000;

--step3:插入数据; expect: 执行成功
begin
	for i in 1..1000 loop
	insert into t_ustore_count_optim_0012(c_id,c_int,c_bool,c_real,c_char,c_timestamp) values(seq_ustore_count_optim_0012.nextval,i+3,'true',i%16+4.9690546872 ,lpad('ee2t2xnbzzkih',50,'et'),to_timestamp('19-aug-2004 21:38:09.123000','dd-mon-yyyy hh24:mi:ss.ff') );
    end loop;
end;
/

--step4:表约束-unique; expect:执行成功
alter table t_ustore_count_optim_0012 add constraint cond_count_optim_0043 unique(c_int);
select c_timestamp,count(c_int) from t_ustore_count_optim_0012 group by c_timestamp having count(2) > 2 order by 1 limit 5;
explain analyze select c_timestamp,count(c_int) from t_ustore_count_optim_0012 group by c_timestamp having count(2) > 2 order by 1 limit 5;

--step5:清理环境; expect:执行成功
drop table if exists t_ustore_count_optim_0012;
drop sequence if exists seq_ustore_count_optim_0012;